<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Proc_reading_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_active(){
      $sql = "SELECT account_id, acct_num, acct_name FROM ar_account WHERE status = 'ACT' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function geth($data){
      $this->db->query('call ubs_get_senior_discount(?,?,@a,@b,@c,@d)', $data);
      $result = $this->db->query('SELECT @a as discount_amt, @b as status, @c as err, @d as msg');
      return $result->result();
      
    }

    public function get_max_readdate($id){
      $sql = "SELECT reading_id FROM ubs_meter_readings WHERE reading_id = (SELECT  MAX(reading_id) FROM ubs_meter_readings WHERE account_id = '".$id."')";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function ifpay($id){
      $sql = "SELECT * FROM ar_payments ap WHERE invoice_id = (SELECT invoice_id FROM ubs_invoices WHERE invoice_num = (SELECT bill_id FROM ubs_meter_readings WHERE reading_id = '".$id."'))";
      $q = $this->db->query($sql);
      return $q->result();
    }

    public function get_ubs_mtr($id,$date){
       $sql = "SELECT umr.reading_id
                     ,umr.bill_id
                     ,umr.account_id
                     ,aa.acct_num
                     ,aa.acct_name
                     ,umr.reading_date
                  FROM `ubs_meter_readings` umr
                    INNER JOIN ar_account aa ON aa.account_id = umr.account_id
                  WHERE umr.account_id = '".$id."' and CONCAT(umr.bill_year,'-',umr.bill_month) = '".$date."' ";
       // $sql="SELECT urs.line_id line_id, urs.rate_id rate_id, uce.charge_name charge_name, urs.description description, urs.usage_qty usage_qty, urs.rate rate FROM ubs_rate_schedule urs INNER JOIN ubs_chg_elements uce ON uce.charge_code = urs.charge_code WHERE urs.rate_id = '".$id."' ";
        $query = $this->db->query($sql);
          if($query->num_rows() != 0){
            return $query->result();
          }
          else {
            $result = "No results found.";
            return $result;
          }
    }

    public function get_read_data($id){
      $sql = "SELECT umr.prev_date
                    ,umr.account_id
                    ,umr.reading_id
                    ,umr.reading_date
                    ,umr.due_date
                    ,aa.acct_num
                    ,aa.acct_name
                    ,ucl.street_address
                    ,umr.prev_read
                    ,umr.meter_reading
                    ,umr.consumed
                    ,umr.read_amt
                    ,umr.bill_bal
                    ,umr.curchg
                    ,umr.total_due
              FROM ubs_meter_readings umr 
                  INNER JOIN ar_account aa ON aa.account_id = umr.account_id 
                  INNER JOIN ubs_cust_latest_readings_v ucl ON ucl.account_id = umr.account_id 
              WHERE umr.reading_id = '".$id."' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function get_latest_read($id){
      $sql = "SELECT * FROM ubs_cust_latest_readings_v WHERE account_id = '".$id."' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function get_bill_bal($id){
      return $this->db->query('SELECT * FROM ubs_invoices WHERE customer_id = '.$id.' AND invoice_num = (SELECT MAX(invoice_num) FROM ubs_invoices WHERE customer_id = '.$id.')')->result();
    }

    public function acct_rates($id){
      $sql = "SELECT * FROM ubs_rates_mobile_v urm WHERE urm.list_id = (SELECT list_id FROM ar_account_chargelist where account_id = ".$id.")";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function acct_chrge($id){
      $sql = "SELECT * FROM `ubs_acct_latest_chgs_v` WHERE acct_id = ".$id." ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function acct_chrge1($id){
      $sql = "SELECT uil.`charge_code`,uil.`line_amt`,uce.`description`, uce.`charge_name` FROM ubs_invoice_lines uil
              LEFT JOIN ubs_chg_elements uce ON uce.charge_code = uil.charge_code
              WHERE uil.invoice_id = ( SELECT invoice_id FROM ubs_invoices WHERE invoice_num = (SELECT bill_id FROM ubs_meter_readings WHERE reading_id = '".$id."') ) AND uil.charge_code <> 'WATCONS' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function used_acct_chrge($id,$rid){
      $sql = "SELECT * FROM `ubs_used_charges` WHERE account_id = ".$id." AND reading_id = '".$rid."' ";
      $query = $this->db->query($sql);
      return $query->result();
    }


    public function save_reads($data){
      $this->db->query('call ubs_water_readings_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@a,@b,@c)', $data);
      $result = $this->db->query('SELECT @a as status');
      return $result->result();
    }

    public function edit_reads($data){
      $this->db->query('call ubs_meter_readings_upd(?,?,?,?,?,?,?,?,?,?)', $data);
      $result = $this->db->query('SELECT @a as status');
      return $result->result();
    }

    public function insert_used_chg($data){
      $this->db->query('call ubs_used_charges_ins(?,?,?,?,?,?,@err,@msg)',$data);
      $out = $this->db->query('SELECT @err as status , @msg as msg');
      return $out->result();
    }
}